from datetime import datetime
from typing import Optional

from sqlalchemy import desc
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from core.database import SessionLocal
from core.logger import log
from db.mysql.call_task_customers import CallTaskCustomers
from db.mysql.call_tasks import CallTasks
from db.mysql.customers import Customers


class CallTaskDao:
    """轮呼拨号任务数据访问类"""
    def __init__(self, db: Session = None):
        self.db = db or SessionLocal()
    def create_call_task(self, call_task_data: dict,auto_commit=True) -> Optional[CallTasks]:
        """
        创建新轮呼任务

        Args:
            call_task_data: 客户数据字典

        Returns:
            CallTasks: 创建的客户对象或 None
        """
        try:
            db_call_task = CallTasks(**call_task_data)
            self.db.add(db_call_task)
            if auto_commit:
                self.db.commit()
                self.db.refresh(db_call_task)
            else:
                # 在非自动提交模式下也尝试 refresh（如果 session 仍然有效）
                self.db.flush()
            log.info(f"轮呼任务创建成功: {db_call_task.task_name}")
            return db_call_task
        except SQLAlchemyError as e:
            log.error(f"创建客户失败: {str(e)}")
            if auto_commit:
                self.db.rollback()
            else:
                raise  # 重新抛出异常让外层处理

    def get_call_tasks(self, user_id:str,page: int = 1, page_size: int = 10, status: Optional[str] = None, created_by: Optional[str] = None):
        """
        获取轮呼任务列表
        Args:
            page: 页码
            page_size: 每页数量
            status: 任务状态
            created_by: 创建人

        Returns:
            list[CallTasks]: 轮呼任务列表
        """
        try:
            query = self.db.query(CallTasks).filter(CallTasks.status.notin_(["cancelled"]))

            # 只有当user_id不为None时才添加用户过滤条件
            if user_id is not None:
                query = query.filter(CallTasks.user_id == user_id)

            if status:
                query = query.filter(CallTasks.status == status)
            if created_by:
                query = query.filter(CallTasks.created_by == created_by)

            # 添加排序
            query = query.order_by(desc(CallTasks.created_at))
            # 获取总记录数
            total_count = query.count()
            # 计算偏移量
            skip = (page - 1) * page_size
            #分页查询
            call_tasks = query.offset(skip).limit(page_size).all()
            return call_tasks, total_count
        except SQLAlchemyError as e:
            log.error(f"获取轮呼任务列表失败: {str(e)}")
            raise  # 重新抛出异常让外层处理

    def get_call_task_by_task_id(self, task_id: int):
        """
        获取轮呼任务详情
        Args:
            task_id: 任务ID

        Returns:
            CallTasks: 轮呼任务对象
        """
        try:
            query = self.db.query(CallTasks).filter(CallTasks.id == task_id).first()
            return query
        except SQLAlchemyError as e:
            log.error(f"获取轮呼任务详情失败: {str(e)}")
            raise

    def get_call_task_customers(self, task_id: int, page: int = 1, page_size: int = 10,
                                call_status: Optional[str] = None):
        try:
            # 先查询符合条件的 CallTaskCustomers
            subquery = self.db.query(CallTaskCustomers.customer_id).filter(
                CallTaskCustomers.task_id == task_id
            )

            if call_status:
                subquery = subquery.filter(CallTaskCustomers.call_status == call_status)

            # 使用 scalar_subquery() 明确转换为标量子查询（推荐方式）
            subquery = subquery.scalar_subquery()

            # 基于子查询结果查询客户信息
            query = self.db.query(Customers).filter(
                Customers.id.in_(subquery)
            )

            total_count = query.count()
            skip = (page - 1) * page_size
            customers = query.offset(skip).limit(page_size).all()
            return customers, total_count
        except SQLAlchemyError as e:
            log.error(f"获取任务关联的客户列表失败: {str(e)}")
            raise

    def get_call_task_all_customers(self,task_id:int):
        try:
            query = self.db.query(Customers).join(
                CallTaskCustomers,
                Customers.customer_id == CallTaskCustomers.customer_id
            ).filter(CallTaskCustomers.task_id == task_id)
            customers = query.all()
            total = query.count()
            return customers, total
        except SQLAlchemyError as e:
            log.error(f"获取任务关联的客户列表失败: {str(e)}")
            raise


    def get_call_task_customers_with_status(self, task_id: int, page: int = 1, page_size: int = 10,
                                            call_status: Optional[str] = None):
        try:
            query = self.db.query(
                Customers,
                CallTaskCustomers.call_status,
                CallTaskCustomers.call_result,
                CallTaskCustomers.call_time,
                CallTaskCustomers.connected_time,
                CallTaskCustomers.completed_time,
                CallTaskCustomers.call_duration
            ).join(
                CallTaskCustomers,
                Customers.customer_id == CallTaskCustomers.customer_id
            ).filter(CallTaskCustomers.task_id == task_id)

            if call_status:
                query = query.filter(CallTaskCustomers.call_status == call_status)

            total_count = query.count()
            skip = (page - 1) * page_size
            results = query.offset(skip).limit(page_size).all()
            return results, total_count
        except SQLAlchemyError as e:
            log.error(f"获取任务关联的客户列表失败: {str(e)}")
            raise

    def get_all_call_task_customers(self, task_id: int):
        """
        获取任务关联的所有客户
        Args:
            task_id: 任务ID

        Returns:
            list[Customers]: 任务关联的所有客户列表
        """
        try:
            query = self.db.query(Customers).join(
                CallTaskCustomers,
                Customers.customer_id == CallTaskCustomers.customer_id
            ).filter(CallTaskCustomers.task_id == task_id)
            customers = query.all()
            total = query.count()
            return customers, total
        except SQLAlchemyError as e:
            log.error(f"获取任务关联的客户列表失败: {str(e)}")
            raise

    def update_call_task(self, task_id: int, call_task_data: dict, auto_commit=True):
        """
        更新轮呼任务
        Args:
            task_id: 任务ID
            call_task_data: 任务数据字典

        Returns:
            CallTasks: 更新后的任务对象
        """
        try:
            db_call_task = self.db.query(CallTasks).filter(CallTasks.id == task_id).first()
            if db_call_task:
                for key, value in call_task_data.items():
                    setattr(db_call_task, key, value)
                if auto_commit:
                    self.db.commit()
                    self.db.refresh(db_call_task)
                else:
                    self.db.flush()
                log.info(f"轮呼任务更新成功: {db_call_task.task_name}")
                return db_call_task
            raise ValueError("任务不存在")
        except SQLAlchemyError as e:
            log.error(f"更新轮呼任务失败: {str(e)}")
            if auto_commit:
                self.db.rollback()
            else:
                raise  # 重新抛出异常让外层处理

    def reduce_task_number(self, task_id: int,auto_commit = True):
        """
        任务数量减一
        Args:
            task_id: 任务ID

        Returns:
            None
        """
        try:
            call_task = self.db.query(CallTasks).filter(CallTasks.id==task_id).first()
            call_task.remaining_count -= 1
            if auto_commit:
                self.db.commit()
                self.db.refresh(call_task)
            else:
                self.db.flush()
        except SQLAlchemyError as e:
            log.error(f"任务数量减一失败: {str(e)}")
            if auto_commit:
                self.db.rollback()
            else:
                raise

class CallTaskCustomerDao:
    """轮呼拨号任务数据客户记录关联访问类"""

    def __init__(self, db: Session = None):
        self.db = db or SessionLocal()

    def create_call_task_customer(self, call_task_cust_data: dict) -> Optional[CallTaskCustomers]:
        """
        创建轮呼任务关联的客户
        Args:
            call_task_cust_data: 轮呼客户数据字典

        Returns:
            CallTaskCustomers: 创建的客户对象或 None
        """
        try:
            db_call_task_cust = CallTaskCustomers(**call_task_cust_data)
            self.db.add(db_call_task_cust)
            self.db.commit()
            self.db.refresh(db_call_task_cust)
            return db_call_task_cust
        except SQLAlchemyError as e:
            self.db.rollback()
            log.error(f"创建轮呼任务关联客户失败: {str(e)}")
            return None

    def bulk_insert_call_task_customers(self, data_list: list, auto_commit=True):
        """批量插入通话任务客户关联记录"""
        try:
            # 直接使用模型类进行 bulk 插入
            self.db.bulk_insert_mappings(CallTaskCustomers, data_list)
            if auto_commit:
                self.db.commit()
        except Exception as e:
            log.error(f"批量插入轮呼任务客户失败: {str(e)}")
            if auto_commit:
                self.db.rollback()
            else:
                raise  # 重新抛出异常让外层处理

    def get_call_task_customer_ids(self,task_id:int,call_status:Optional[str] = None):
        try:
            query = self.db.query(CallTaskCustomers.customer_id)
            query = query.filter(CallTaskCustomers.task_id == task_id)
            if call_status:
                query = query.filter(CallTaskCustomers.call_status == call_status)
            customer_ids = query.all()
            return [customer_id[0] for customer_id in customer_ids]
        except SQLAlchemyError as e:
            log.error(f"获取任务关联的客户ID列表失败: {str(e)}")
            raise

    def get_task_customers(self,task_id:int):
        try:
            task_customers_list = self.db.query(CallTaskCustomers).filter(CallTaskCustomers.task_id == task_id).all()
            return task_customers_list
        except SQLAlchemyError as e:
            log.error(e)
            return None

    def update_task_customer(self,task_id:int,customer_id:str,data:dict,auto_commit=True):
        try:
            task_customer = self.db.query(CallTaskCustomers).filter(CallTaskCustomers.task_id == task_id,CallTaskCustomers.customer_id == customer_id).first()
            if task_customer:
                for key,value in data.items():
                    setattr(task_customer,key,value)
                    # 当设置 completed_time 时，计算通话时长
                    if key == "completed_time" and task_customer.connected_time and value:
                        try:
                            # 将 connected_time 转换为 datetime 对象（如果还不是的话）
                            if isinstance(task_customer.connected_time, str):
                                conn_time_str = task_customer.connected_time.replace('Z',
                                                                                     '+00:00') if 'Z' in task_customer.connected_time else task_customer.connected_time
                                connected_time = datetime.fromisoformat(conn_time_str)
                            else:
                                connected_time = task_customer.connected_time

                            # 将 value 转换为 datetime 对象（如果还不是的话）
                            if isinstance(value, str):
                                comp_time_str = value.replace('Z', '+00:00') if 'Z' in value else value
                                completed_time = datetime.fromisoformat(comp_time_str)
                            else:
                                completed_time = value

                            # 计算通话时长
                            duration = (completed_time - connected_time).total_seconds()
                            setattr(task_customer, "call_duration", max(0, int(duration)))  # 确保非负
                        except (ValueError, TypeError) as e:
                            log.error(f"计算通话时长失败: {str(e)}")
                            setattr(task_customer, "call_duration", 0)
                if auto_commit:
                    self.db.commit()
                    self.db.refresh(task_customer)
                else:
                    self.db.flush()
                log.info(f"更新任务关联的客户状态成功: {task_customer.customer_id}")
                return task_customer
            raise ValueError("任务关联的客户不存在")
        except SQLAlchemyError as e:
            log.error(f"更新任务关联的客户状态失败: {str(e)}")
            if auto_commit:
                self.db.rollback()
            raise
